<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="z" uri="zz"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <%@include file="/views/common/common.jsp"%>

    <!-- 树型控件 -->
    <link href="<%=request.getContextPath()%>/js/zTree_v3/css/metroStyle/metroStyle.css" type="text/css" rel="stylesheet">
    <script src="<%=request.getContextPath()%>/js/zTree_v3/js/jquery.ztree.core-3.5.js" type="text/javascript"></script>
    <script src="<%=request.getContextPath()%>/js/zTree_v3/js/jquery.ztree.excheck-3.5.js" type="text/javascript" ></script>
    <script src="<%=request.getContextPath()%>/js/zTree_v3/js/jquery.ztree.exedit-3.5.js" type="text/javascript"></script>

    <%-- SQL格式化工具--%>
    <script type="text/javascript" src="<%=request.getContextPath()%>/js/sql-formatter.min.js?version=${sp.zversion}" charset="UTF-8"></script>

    <script type="text/javascript">
        var sql_hint_map;
        var ColumnEditor;

        //初始化方法
        $(document).ready(function () {
            //初始化所有快捷键
            init_hotkeys();

            //加载树
            init_tree();

            //初始化监听事件
            init_Listener();
        });

        //执行SQL
        function RunSQL(){
            var datasource = $("#dbid_id").val();//数据源ID
            var db = $("#dbselect").val();//数据库
            var sqlinfo = $("#sqlinfo_id").val();//SQL信息
            var userid = $("#now_user_zid").val();//用户ID
            var isLimit = $('#isLimitChecked').is(":checked");//是否分页
            if(isNotNull(sqlinfo)){

                //关闭所有Tab
                closeAllTab();
                openLoading();
                $.ajax({
                    type: "get",
                    url: "RunSQL",
                    data: {sqlinfo: sqlinfo,datasource:datasource,userid:userid,isLimit:isLimit,db:db},
                    success: function (data) {
                        closedLoading();
                        if (data.code == 'SUCCESS') {
                            for (var i = 0; i < data.data.rlist.length; i++) {
                                var result = data.data.rlist[i];
                                var n = i+1;

                                if(result.SqlType=='select'){
                                    if(result.code=='SUCCESS'){
                                        addSqlRunResultTab("select结果"+n,CreateSelectResultTable(result.data))
                                    }else{
                                        addSqlRunResultTab("select结果"+n,"<h5>"+result.msg+"</h5>")
                                    }
                                }else if(result.SqlType=='show'){
                                    if(result.code=='SUCCESS'){
                                        addSqlRunResultTab("show结果"+n,CreateSelectResultTable(result.data))
                                    }else{
                                        addSqlRunResultTab("show结果"+n,"<h5>"+result.msg+"</h5>")
                                    }
                                }else if(result.SqlType=='insert'){
                                    addSqlRunResultTab("insert结果"+n,"<h5>"+result.msg+"</h5>")
                                }else if(result.SqlType=='update'){
                                    addSqlRunResultTab("update结果"+n,"<h5>"+result.msg+"</h5>")
                                }else if(result.SqlType=='delete'){
                                    addSqlRunResultTab("delete结果"+n,"<h5>"+result.msg+"</h5>")
                                }else{
                                    addSqlRunResultTab("结果"+n,"<h5>"+result.msg+"</h5>")
                                }
                            }

                            //生成SQL执行记录列表
                            CreateRunSqlList(data.data.run_sql_list);


                        } else {
                            alertErrorMessager('' + data.msg);
                        }
                    },
                    error: function (data) {
                        alertErrorMessager('ajax错误：' + JSON.stringify(data));
                    }
                });
            }else{
                alertMessagerAutoOff('请输入要执行的SQL');
            }

            //设置焦点
            ColumnEditor.focus();
        }

        //创建select结果显示列表
        function CreateSelectResultTable(table_data){

            //表头JSON
            var tableHeadJson = table_data.tableHeadJson;

            //表体JSON
            var tableBodyJson = table_data.tableBodyJson;

            let table = document.createElement('table');
            table.className = 'easyui-datagrid';
            table.setAttribute("data-options","rownumbers:true,ctrlSelect:true,fit:true,nowrap:false");

            let thead = document.createElement('thead');
            let tbody = document.createElement('tbody');
            let headers = Object.keys(tableHeadJson);

            // 创建表头
            let theadTr = document.createElement('tr');
            headers.forEach(header => {
                let th = document.createElement('th');
                th.setAttribute('data-options','field:"'+header+'"');
                th.appendChild(document.createTextNode(header));
                theadTr.appendChild(th);
            });
            thead.appendChild(theadTr);
            table.appendChild(thead);

            // 创建表身
            tableBodyJson.forEach(row => {
                let tr = document.createElement('tr');

                headers.forEach(header => {
                    let td = document.createElement('td');
                    var tdtext = "";
                    if(isNotNull(row[header])){
                        tdtext = row[header];
                    }
                    td.appendChild(document.createTextNode(tdtext));
                    tr.appendChild(td);
                });
                tbody.appendChild(tr);
            });
            table.appendChild(tbody);

            return table;
        }



        /**
         * 创建SQL执行显示结果Tab
         * @param number
         * @param title
         * @param url
         */
        function addSqlRunResultTab(tab_title, htmlinfo) {
            $('#SqlRunResultTab').tabs('add', {
                title: tab_title,
                content: htmlinfo,
                closable: true
            });
        }

        /**
         * 关闭所有Tab
         */
        function closeAllTab(){
            var tablist = $('#SqlRunResultTab').tabs('tabs');
            var length = tablist.length;
            for (var i = 0; i <length; i++) {
                $('#SqlRunResultTab').tabs('close', 0);
            }
        }


        /**
         * 初始化所有快捷键
         */
        function init_hotkeys() {
            document.addEventListener('keydown', function(event) {
                if (event.key === 'F8') {
                    document.getElementById('RunSQLButton').focus();
                    RunSQL();
                }

                if (event.ctrlKey && (event.key === 'i' || event.key === 'I')) {
                    document.getElementById('SQLFormatterButton').focus();
                    SQLFormatter();
                }

                if (event.ctrlKey && (event.key === 'd' || event.key === 'D')) {
                    document.getElementById('SQLClearButton').focus();
                    SQLClear();
                }

            });

        }


        //监听数据源变化
        function DataSourceOnChange(){
            init_tree();
        }

        //初始化ColumnEditor
        function init_ColumnEditor(){
            ColumnEditor = CodeMirror.fromTextArea(document.getElementById('sqlinfo_id'), {
                lineNumbers: true,//显示行号
                matchBrackets: true,//括号匹配
                smartIndent: true,//自动缩进是否开启
                theme: 'eclipse', //编辑器主题
                indentWithTabs: true,
                autofocus: true,
                styleSelectedText: true,
                mode: 'text/x-sql',//编辑器语言
                hintOptions: {
                    // 当匹配只有一项的时候是否自动补全
                    completeSingle: true,
                    tables:sql_hint_map
                },
                extraKeys: {
                    "'a'": completeAfter,
                    "'b'": completeAfter,
                    "'c'": completeAfter,
                    "'d'": completeAfter,
                    "'e'": completeAfter,
                    "'f'": completeAfter,
                    "'g'": completeAfter,
                    "'h'": completeAfter,
                    "'i'": completeAfter,
                    "'j'": completeAfter,
                    "'k'": completeAfter,
                    "'l'": completeAfter,
                    "'m'": completeAfter,
                    "'n'": completeAfter,
                    "'o'": completeAfter,
                    "'p'": completeAfter,
                    "'q'": completeAfter,
                    "'r'": completeAfter,
                    "'s'": completeAfter,
                    "'t'": completeAfter,
                    "'u'": completeAfter,
                    "'v'": completeAfter,
                    "'w'": completeAfter,
                    "'x'": completeAfter,
                    "'y'": completeAfter,
                    "'z'": completeAfter,
                    "'.'": completeAfter
                }
            });
            ColumnEditor.on("blur", function () {
                $('#sqlinfo_id').val(ColumnEditor.getValue());
            });
            //默认高度
            ColumnEditor.setSize('100%', '400px');
        }
        function completeAfter(cm, pred) {
            var cur = cm.getCursor();
            if (!pred || pred()) setTimeout(function() {
                if (!cm.state.completionActive)
                    cm.showHint({
                        completeSingle: false
                    });
            }, 100);
            return CodeMirror.Pass;
        }



        function init_tree(){
            var datasource = $("#dbid_id").val();//数据库ID
            var setting = {
                data : {
                    simpleData : {
                        enable : true,
                        idKey : "zid",
                        pIdKey : "parentid",
                        rootPId : 0
                    }
                },
                check: {
                    enable: false
                },
                callback:{
                    onRightClick:OnRightClick
                }
            };


            $.ajax({
                async : false,
                type : "get",
                url : "getDbTree?datasource="+datasource,
                dataType : "json",
                success : function(data) {
                    //设置数据库信息
                    $.fn.zTree.init($("#dbtree"), setting, data.data.treeList);

                    //当前库
                    var now_db = data.data.now_db;

                    //sql提示信息
                    sql_hint_map = data.data.sql_hint_map;

                    //设置选择库
                    var dbselect = document.getElementById('dbselect');
                    dbselect.options.length = 0;
                    data.data.dbs.forEach(function(item) {
                        var option = document.createElement('option');
                        option.value = item.zid;
                        option.text = item.zid;
                        if(item.zid==now_db){
                            option.selected = true;
                        }
                        dbselect.appendChild(option);
                    });

                    //初始化ColumnEditor
                    init_ColumnEditor();

                    //创建SQL执行记录
                    CreateRunSqlList(data.data.run_sql_list);

                    //创建SQL收藏记录列表
                    CreateSqlCollectsList(data.data.sql_collects_list);
                }
            });
        }


        /**
         * 弹出右键菜单
         *
         * */
        function OnRightClick(event,treeId,treeNode){
            var dbid = $("#dbid_id").val();//数据源ID
            var dbname = treeNode.zid;
            if('db'==treeNode.nodeType){

                //集合清空
                $('#RightMenu').empty();

                //建设菜单项
                $('#RightMenu').menu('appendItem',{id:dbname+"_backdb",text:"备份数据库",iconCls:"fa fa-download"});
                $("#"+dbname+"_backdb").click(function(){
                    BackupDb(dbid,dbname);
                });

                //显示db菜单
                $('#RightMenu').menu('show', {left: event.clientX,top: event.clientY,hideOnUnhover:false});

            }else if('table'==treeNode.nodeType){
                //$('#RightTableMenu').menu('show', {left: event.clientX,top: event.clientY,hideOnUnhover:false});
            }
        }

        /**
         * 备份数据库
         */
        function BackupDb(dbid,dbname){
            // 创建form元素
            var $form = $('<form>', {
                action: 'BackupDb?dbid='+dbid+'&dbname='+dbname,
                method: 'POST'
            });

            // 将form添加到DOM中
            $('body').append($form);

            // 提交form
            $form.submit();
        }

        /**
         * 创建SQL执行记录
         */
        function CreateRunSqlList(listdata){
            var ul = document.getElementById('runSqlList');

            //清空ul中的li
            while (ul.firstChild) {
                ul.removeChild(ul.firstChild);
            }

            //生成新的li
            for (var i = 0; i < listdata.length; i++) {
                var li = document.createElement('li');
                li.textContent = listdata[i].sqlinfo;
                li.className = "list-group-item";
                ul.appendChild(li);
            }
        }

        /**
         * 创建SQL收藏记录
         */
        function CreateSqlCollectsList(listdata){
            var ul = document.getElementById('collectsList');

            //清空ul中的li
            while (ul.firstChild) {
                ul.removeChild(ul.firstChild);
            }

            //生成新的li
            for (var i = 0; i < listdata.length; i++) {
                var li = document.createElement('li');
                li.textContent = listdata[i].name;
                li.setAttribute('sql',listdata[i].sqlinfo);
                li.className = "list-group-item";
                ul.appendChild(li);
            }
        }

        //初始化监听事件
        function init_Listener(){

            //监听SQL执行列表双击事件
            $('#runSqlList').on('dblclick', 'li', function() {

                //获取光标位置
                var cursor = ColumnEditor.getCursor();
                if(isNotNull(cursor) && isNotNull(cursor.line) && cursor.line>=0){
                    // 插入文本
                    ColumnEditor.replaceRange($(this).text(), cursor);

                    // 设置光标位置到最后
                    ColumnEditor.focus(); // 先让textarea获得焦点

                    // 设置selectionStart和selectionEnd
                    ColumnEditor.setCursor(cursor.line, ColumnEditor.getValue().length);
                }else{
                    // 获取当前编辑器的内容
                    var oldContent = ColumnEditor.getValue();

                    // 追加新内容
                    var newContent = oldContent + $(this).text();

                    // 设置新的内容
                    ColumnEditor.setValue(newContent);

                    // 设置光标位置到最后
                    ColumnEditor.focus(); // 先让textarea获得焦点

                    // 设置selectionStart和selectionEnd
                    ColumnEditor.setCursor(newContent.length, newContent.length);
                }

            });


            //监听-收藏列表双击事件
            $('#collectsList').on('dblclick', 'li', function() {

                var value = $(this).attr('sql');

                //获取光标位置
                var cursor = ColumnEditor.getCursor();
                if(isNotNull(cursor) && isNotNull(cursor.line) && cursor.line>=0){
                    // 插入文本
                    ColumnEditor.replaceRange(value, cursor);

                    // 设置光标位置到最后
                    ColumnEditor.focus(); // 先让textarea获得焦点

                    // 设置selectionStart和selectionEnd
                    ColumnEditor.setCursor(cursor.line, ColumnEditor.getValue().length);
                }else{
                    // 获取当前编辑器的内容
                    var oldContent = ColumnEditor.getValue();

                    // 追加新内容
                    var newContent = oldContent + value;

                    // 设置新的内容
                    ColumnEditor.setValue(newContent);

                    // 设置光标位置到最后
                    ColumnEditor.focus(); // 先让textarea获得焦点

                    // 设置selectionStart和selectionEnd
                    ColumnEditor.setCursor(newContent.length, newContent.length);
                }

            });
        }

        /**
         * 数据库点击事件
         * @param dbname
         * @constructor
         */
        function DbOnClick(dbname){
            if(isNotNull(dbname)){
                $('#dbselect').val(dbname);
            }
        }

        /**
         * SQL格式化
         * @constructor
         */
        function SQLFormatter(){
            var sqlinfo = ColumnEditor.getValue();//SQL信息
            if(isNotNull(sqlinfo)){
                var formattedSQL = sqlFormatter.format(sqlinfo);
                ColumnEditor.setValue(formattedSQL);
                ColumnEditor.focus();
            }
        }

        /**
         * 清空SQL
         * @constructor
         */
        function SQLClear(){
            ColumnEditor.setValue('');
            ColumnEditor.focus();
        }

        /**
         * 收藏SQL
         * @constructor
         */
        function SQLCollect(){
            var sqlinfo = $("#sqlinfo_id").val();//SQL信息
            var userid = $("#now_user_zid").val();//用户ID
            if(isNotNull(sqlinfo)){
                $.messager.prompt('收藏SQL', '请输入SQL描述名称：', function (r) {
                    if (isNotNull(r)) {
                        openLoading();
                        $.ajax({
                            type: "get",
                            url: "SQLCollect",
                            data: {sqlinfo: sqlinfo,userid:userid,name:r},
                            success: function (data) {
                                closedLoading();
                                if (data.code == 'SUCCESS') {
                                    CreateSqlCollectsList(data.data);
                                    alertMessagerAutoOff("收藏成功")
                                } else {
                                    alertErrorMessager('' + data.msg);
                                }
                            },
                            error: function (data) {
                                alertErrorMessager('ajax错误：' + JSON.stringify(data));
                            }
                        });
                    }
                });

            }else{
                alertMessagerAutoOff('请输入要收藏的SQL');
            }

            //设置焦点
            ColumnEditor.focus();
        }



    </script>
</head>
<body class="easyui-layout">
<div data-options="region:'west',collapsible:'false',split:true" style="width: 25%;">
    <div class="easyui-layout" data-options="fit:true">
        <div data-options="region:'north',border:false,split:true" class="" style="height:100px">
            <div class="container-fluid">
                <div class="row mt-2 mb-2">
                    <div class="col-md-12 mb-2 d-flex align-items-start">
                        <div class="input-group input-group-sm">
                            <div class="input-group-prepend"><div class="input-group-text ">数据源：</div></div>
                            <input id="dbid_id" name="dbid" type="hidden"  value="${datasource}" />
                            <input  placeholder="数据源" id="dbid_display" onclick="Z5list('z_form_table_dbid','dbid_id','dbid_display',0)" name="dbid_display" value="平台核心库" type="text" class="form-control marginleft1 border-right-0"  onchange="DataSourceOnChange()" readonly="readonly" />
                            <span class="input-group-prepend marginright1 border-top border-right border-bottom">
                            <button class="btn btn-light" onclick="Z5Clear('z_form_table_dbid','dbid_id','dbid_display')" type="button" ><i class="fa fa-trash-o"></i></button>
                            <button class="btn btn-light" onclick="Z5list('z_form_table_dbid','dbid_id','dbid_display',0)" type="button" ><i class="fa fa-search"></i></button>
                            </span>
                        </div>
                    </div>
                </div>
                <div class="row mt-2 mb-2">
                    <div class="col-12">
                        <div class="input-group input-group-sm">
                            <div class="input-group-prepend">
                                <div class="input-group-text ">库：</div>
                            </div>
                            <select class="form-control form-control-sm" id="dbselect">
                            </select>
                        </div>
                    </div>
                </div>

            </div>
        </div>
        <div data-options="region:'center',split:true">
            <div class="container-fluid">
                <div class="row">
                    <ul id="dbtree" class="ztree"></ul>
                </div>
            </div>

        </div>
        <div data-options="region:'south',split:true" style="height:30%;">
            <div id="sqlTreeTab" class="easyui-tabs" style="height: 100%">
                <div title="执行记录" iconCls="fa fa-reorder"><ul class="list-group" id="runSqlList"></ul></div>
                <div title="收藏SQL" iconCls="fa fa-heart"><ul class="list-group" id="collectsList"></ul></div>
            </div>
        </div>
    </div>


</div>

<div data-options="region:'center',border:false">
    <div class="easyui-layout" data-options="fit:true">
        <div data-options="region:'north',border:false,split:true" class="border-top-0 border-right-0 border-left-0" style="height:440px;overflow:hidden">
            <div class="container-fluid">
                <div class="row d-flex justify-content-between">
                    <div class="col-9">
                        <div id="ButtonHR" class="btn-group" role="group">
                            <button id="RunSQLButton" type="button" class="btn btn-light" onclick="RunSQL();"><i class="fa fa-play"></i> 运行(F8)</button>
                            <button id="SQLFormatterButton" type="button" class="btn btn-light" onclick="SQLFormatter();"><i class="fa fa-paint-brush"></i> 格式化(Ctrl+I)</button>
                            <button id="SQLClearButton" type="button" class="btn btn-light" onclick="SQLClear();"><i class="fa fa-eraser"></i> 清空(Ctrl+D)</button>
                            <button id="SQLCollectButton" type="button" class="btn btn-light" onclick="SQLCollect();"><i class="fa fa-heart"></i> 收藏</button>
                        </div>
                    </div>
                    <div class="col-md-3 text-right mt-2">
                        <input type="checkbox" class="form-check-input" id="isLimitChecked" checked="${isLimit}">
                        <label class="form-check-label">限制100行</label>
                    </div>
                </div>
                <div class="row">
                    <textarea placeholder="SQL" id="sqlinfo_id" name="sqlinfo"  style="height: 400px;width: 100%;" ></textarea>
                </div>
            </div>
        </div>
        <div data-options="region:'center',split:true">
            <div id="SqlRunResultTab" class="easyui-tabs" style="height: 100%" data-options="border:false">
            </div>
        </div>
    </div>
</div>
<div id="RightMenu" class="easyui-menu" style="width:120px;">
</div>
<%@include file="/views/common/body.jsp"%>
</body>
</html>